package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.entity.base.Sjzdz;
import com.hnkywl.sts.entity.cggl.Glfy;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.entity.xsgl.Xsd;
import com.hnkywl.sts.entity.xsgl.Xsmx;
import com.hnkywl.sts.service.sys.YgService;
import com.hnkywl.sts.util.CalcUtils;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-15
 * Time: 下午2:14
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XsdDao extends SimpleHibernateTemplate<Xsd, Long> {
    private YgDao ygDao;

    private KhDao khDao;

    public Page<Map> findXsdList(String xsdh, Date fhsjStart, Date fhsjEnd, Long xshtId, Long khId, Long gcId, Long sbgsId, Long sbbmId, Long sbrId, String zt, String cxzt, String sftzd, String xslx, Boolean dlkhxsd, int type, int pageNo, int pageSize) throws ParseException {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("xssl").as("xssl"))
                                .add(Property.forName("xsjs").as("xsjs"))
                                .add(Property.forName("orderId").as("orderId"))
                                .add(Property.forName("xsje").as("xsje"))
                                .add(Property.forName("zjfpzje").as("zjfpzje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("xsbhsje").as("xsbhsje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsht.id").as("xsht_id"))
                                .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("this_.date_created as sxsj", new String[]{"sxsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("cwsh").as("cwsh"))
                                .add(Property.forName("kh.qc").as("kh_qc"))
                                .add(Property.forName("kh.jc").as("kh_jc"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("thck.mc").as("thck_mc"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("dsyfje").as("dsyfje"))
                                .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                                .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                                .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                                .add(Property.forName("xsbm.jc").as("xsbm_jc"))
                                .add(Property.forName("xsr.xm").as("xsr_xm"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("yck").as("yck"))
                                .add(Property.forName("endFlag").as("endFlag"))
                                .add(Property.forName("tzsqzt").as("tzsqzt"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.ANYWHERE));
        }

        if (fhsjStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhsjStart)));
        }

        if (fhsjEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhsjEnd)));
        }

        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty()) {
                criteria.add(Restrictions.eq("zt", zt));
            }
        } else {
            // 出库单管理
            if (type == 1) {
                criteria.add(Restrictions.or(Restrictions.eq("zt", "ybc"), Restrictions.eq("zt", "ysh")));
            } else if (type == 2) {
                String[] str = {"ygk", "ytj"};
                criteria.add(Restrictions.in("zt", str));
            } else {
                String[] str = {"ysp", "ypk"};
                criteria.add(Restrictions.in("zt", str));
            }
        }

        if (xshtId != null) {
            criteria.add(Restrictions.eq("xsht.id", xshtId));
        }
        if (sbgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        }
        if (sbbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", sbbmId));
        }
        if (sbrId != null) {
            criteria.add(Restrictions.eq("sbr.id", sbrId));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (gcId != null) {
            criteria.add(Restrictions.eq("gc.id", gcId));
        }

        /*if (sftzd != null) {
            criteria.add(Restrictions.ne("xslx", "nbxs"));
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }*/

      /*  if (StringUtils.isNotEmpty(xslx)) {
            criteria.add(Restrictions.eq("xslx", xslx));
        }*/

       /* if (dlkhxsd != null && dlkhxsd) {
            DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Nbgs.class);
            detachedCriteria
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("id").as("id")));
            criteria.add(Property.forName("sbgs.id").in(detachedCriteria));
        }*/

        createXsdDataRegion(criteria);
        if (type == 2) {
            criteria.addOrder(Property.forName("zt").desc());
            criteria.addOrder(Property.forName("lastUpdated").desc());
        } else {
            criteria.addOrder(Property.forName("lastUpdated").desc());
            criteria.addOrder(Property.forName("id").desc());
            criteria.addOrder(Property.forName("yxsd.id").desc());
        }

        Page list = this.findByCriteria(page, criteria);
        return list;
    }
    /**
     * 销售合同 销售记录查询
     * @param xsdh
     * @param fhsjStart
     * @param fhsjEnd
     * @param xshtId
     * @param khId
     * @param gcId
     * @param sbgsId
     * @param sbbmId
     * @param sbrId
     * @param zt
     * @param cxzt
     * @param sftzd
     * @param xslx
     * @param dlkhxsd
     * @param pageNo
     * @param pageSize
     * @return
     * @throws ParseException
     */
    public Page<Map> findXsdByXshtList(String xsdh, Date fhsjStart, Date fhsjEnd, Long xshtId, Long khId, Long gcId, Long sbgsId, Long sbbmId, Long sbrId, String zt, String cxzt, String sftzd, String xslx, Boolean dlkhxsd, int pageNo, int pageSize) throws ParseException {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("xssl").as("xssl"))
                                .add(Property.forName("xsjs").as("xsjs"))
                                .add(Property.forName("orderId").as("orderId"))
                                .add(Property.forName("xsje").as("xsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("xsbhsje").as("xsbhsje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsht.id").as("xsht_id"))
                                .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("xslx as xslx", new String[]{"xslx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("this_.date_created as sxsj", new String[]{"sxsj"}, new Type[]{Hibernate.TIMESTAMP}))
                                .add(Projections.sqlProjection("sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.BOOLEAN}))
                                .add(Property.forName("cwsh").as("cwsh"))
                                .add(Property.forName("kh.qc").as("kh_qc"))
                                .add(Property.forName("kh.jc").as("kh_jc"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("thck.mc").as("thck_mc"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("dsyfje").as("dsyfje"))
                                .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                                .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                                .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                                .add(Property.forName("xsbm.jc").as("xsbm_jc"))
                                .add(Property.forName("xsr.xm").as("xsr_xm"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("yck").as("yck"))
                                .add(Property.forName("endFlag").as("endFlag"))
                                .add(Property.forName("tzsqzt").as("tzsqzt"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.ANYWHERE));
        }

        if (fhsjStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhsjStart)));
        }

        if (fhsjEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhsjEnd)));
        }

        criteria.add(Restrictions.eq("zt", "ypk"));


        if (xshtId != null) {
            criteria.add(Restrictions.eq("xsht.id", xshtId));
        }
        if (sbgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        }
        if (sbbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", sbbmId));
        }
        if (sbrId != null) {
            criteria.add(Restrictions.eq("sbr.id", sbrId));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (gcId != null) {
            criteria.add(Restrictions.eq("gc.id", gcId));
        }

        /*if (sftzd != null) {
            criteria.add(Restrictions.ne("xslx", "nbxs"));
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }*/

      /*  if (StringUtils.isNotEmpty(xslx)) {
            criteria.add(Restrictions.eq("xslx", xslx));
        }*/

       /* if (dlkhxsd != null && dlkhxsd) {
            DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Nbgs.class);
            detachedCriteria
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("id").as("id")));
            criteria.add(Property.forName("sbgs.id").in(detachedCriteria));
        }*/

        createXsdDataRegion(criteria);

        criteria.addOrder(Property.forName("lastUpdated").desc());


        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    public String findAllXsd() {
        Criteria countCriteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.rowCount());
        Date start = sDaysAfterNowDate(new Date());
        countCriteria.add(Restrictions.ge("dateCreated", start));
        Date end = jDaysAfterNowDate(new Date());
        countCriteria.add(Restrictions.le("dateCreated", end));
        countCriteria.add(Restrictions.eq("zt", "ybc"));
        countCriteria.add(Restrictions.ne("xslx", "nbxs"));
        countCriteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        createXsdDataRegion(countCriteria);
        Long totalCount = (Long) countCriteria.uniqueResult();
        return totalCount.toString();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    private Date sDaysAfterNowDate(int n) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_MONTH, -n);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    private void createXsdDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("thck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("thck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("thck", list));
                    break;
            }
        } else {


            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }

            criteria.add(
                    Restrictions.or(se,
                            Restrictions.and(Restrictions.eq("sbgs.id", YgService.getCurrentYgZgGsId()),
                                    Restrictions.eq("zt", "ysp")
                            )
                    )
            );
        }
    }

    public List<Map> findXsdmxSearch(Long xsjsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsjsd", "xsjsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsd.id").as("xsd.id"))
                                .add(Property.forName("xsd.xsdh").as("xsdh"))
                                .add(Property.forName("gc.mc").as("gc.mc"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("kh.xsxt_xsdh").as("yyxsdh"))
                                .add(Property.forName("kh.carNo").as("carNo"))
                                .add(Property.forName("kh.bindNo").as("bindNo"))
                                .add(Property.forName("xscb").as("xscb"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("ck_js").as("ck_js"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck_sbsl").as("cksl"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("kpbhsj").as("kpbhsj"))
                                .add(Property.forName("sjdj").as("sjdj"))
                                .add(Property.forName("sjghje").as("sjghje"))
                                .add(Property.forName("txx").as("txx"))
                                .add(Property.forName("dlf").as("dlf"))
                                .add(Property.forName("zjzyf").as("zjzyf"))
                                .add(Property.forName("xkyh").as("xkyh"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("se").as("fpse"))
                                .add(Property.forName("xsjsd.ce").as("ce"))
                                .add(Property.forName("xsjsd.ddfy").as("ddfy"))
                ).add(Restrictions.eq("xsjsd.id", xsjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return criteria.list();
    }

    public List<Map> findXsmxList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsjsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("cgd.id").as("cgd.id"))
                                .add(Property.forName("cgd.cgdh").as("cgd.cgdh"))
                                .add(Property.forName("kh.khbm").as("kh.khbm"))
                                .add(Property.forName("kh.xsxt_xsdh").as("xxsdh"))
                                .add(Property.forName("kh.kkdsl").as("kkdsl"))
                                .add(Property.forName("kh.resource_serial_number_1").as("resource_serial_number_1"))
                                .add(Property.forName("kh.carNo").as("carNo"))
                                .add(Property.forName("kh.bindNo").as("bindNo"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.oldcode").as("wzgg.oldcode"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Projections.sqlProjection("wzgg3_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("wzgg.th").as("wzgg.th"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("xsd.xsdh").as("xsdh"))
                                .add(Projections.sqlProjection("this_.xsxt_xsdh as \"xsxt_xsdh\"", new String[]{"xsxt_xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("jsfs").as("jsfs"))
                                .add(Property.forName("xscb").as("xscb"))
                                .add(Property.forName("jtwz.id").as("jtwz.id"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("ghje").as("ghje"))
                                .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("sjdj").as("sjdj"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("jsd.id").as("jsd.id"))
                                .add(Property.forName("jsd.jsdh").as("jsdh"))
                                .add(Property.forName("bz").as("bz"))
                                .add(Projections.sqlProjection("mxh as \"mxh\"", new String[]{"mxh"}, new Type[]{Hibernate.LONG}))
                ).add(Restrictions.eq("xsd.id", xsdId)).addOrder(Property.forName("xsxt_xsdh").asc())
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("mxh").asc());
        return (List<Map>) criteria.list();
    }

    //套打获取的销售单明细列表
    public List<Map> findXsmxListforTd(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("kh.khbm").as("kh.khbm"))
                                .add(Property.forName("kh.kkdsl").as("kkdsl"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("xscb").as("xscb"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("ck_js").as("ck_js"))
                                .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("xsd.id", xsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsmxJsList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jfmxList", "jfmxList")
                .setProjection(Projections.projectionList()
                                .add(Projections.groupProperty("id").as("id"))
                                        //  .add(Projections.sqlGroupProjection("xsd5_.id as xsd.id","xsd5_.xsd.id", new String[]{"xsd.id"}, new Type[]{Hibernate.LONG}))
                                .add(Projections.groupProperty("xsd.sl").as("xsd.sl"))
                                .add(Projections.groupProperty("xsd.dsyfje").as("dsyfje"))
                                .add(Projections.groupProperty("kh.id").as("kh.id"))
                                .add(Projections.groupProperty("kh.khbm").as("kh.khbm"))
                                .add(Projections.groupProperty("kh.xsxt_xsdh").as("xxsdh"))
                                .add(Projections.groupProperty("kh.carNo").as("carNo"))
                                .add(Projections.groupProperty("kh.bindNo").as("bindNo"))
                                .add(Projections.groupProperty("kh.kkdsl").as("kkdsl"))
                                .add(Projections.groupProperty("wzgg.id").as("wzgg.id"))
                                .add(Projections.groupProperty("wzgg.code").as("wzgg.code"))
                                .add(Projections.groupProperty("wzgg.cz").as("wzgg.cz"))
                                .add(Projections.groupProperty("wzgg.name").as("wzgg.name"))
                                .add(Projections.groupProperty("wzgg.ggxh").as("wzgg.ggxh"))
                                .add(Projections.groupProperty("wzgg.dw").as("wzgg.dw"))
                                .add(Projections.groupProperty("cd.id").as("cd.id"))
                                .add(Projections.groupProperty("cd.z").as("cd.z"))
                                .add(Projections.groupProperty("xscb").as("xscb"))
                                .add(Projections.groupProperty("sjdj").as("sjdj"))
                                .add(Projections.groupProperty("js").as("js"))
                                .add(Projections.groupProperty("ck_sbsl").as("cksl"))
                                .add(Projections.groupProperty("sbsl").as("sbsl"))
                                .add(Projections.groupProperty("jzfs.id").as("jzfs.id"))
                                .add(Projections.groupProperty("jzfs.z").as("jzfs.z"))
                                .add(Projections.groupProperty("hsj").as("hsj"))
                                .add(Projections.groupProperty("bhsj").as("bhsj"))
                                .add(Projections.groupProperty("jsbhse").as("je"))
                                .add(Projections.groupProperty("jsse").as("se"))
                                .add(Projections.groupProperty("bz").as("bz"))
                                .add(Projections.sum("jfmxList.dlfje").as("jfmxdlf"))
                ).add(Restrictions.eq("xsd.id", xsdId)).add(Restrictions.eq("yjs", false))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsmxJsList1(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                        /// .createAlias("jfmxList", "jfmxList")
                .setProjection(Projections.projectionList()
                                .add(Projections.groupProperty("id").as("id"))
                                        //  .add(Projections.sqlGroupProjection("xsd5_.id as xsd.id","xsd5_.xsd.id", new String[]{"xsd.id"}, new Type[]{Hibernate.LONG}))
                                .add(Projections.groupProperty("xsd.sl").as("xsd.sl"))
                                .add(Projections.groupProperty("xsd.dsyfje").as("dsyfje"))
                                .add(Projections.groupProperty("kh.id").as("kh.id"))
                                .add(Projections.groupProperty("kh.khbm").as("kh.khbm"))
                                .add(Projections.groupProperty("kh.xsxt_xsdh").as("xxsdh"))
                                .add(Projections.groupProperty("kh.carNo").as("carNo"))
                                .add(Projections.groupProperty("kh.bindNo").as("bindNo"))
                                .add(Projections.groupProperty("kh.kkdsl").as("kkdsl"))
                                .add(Projections.groupProperty("wzgg.id").as("wzgg.id"))
                                .add(Projections.groupProperty("wzgg.code").as("wzgg.code"))
                                .add(Projections.groupProperty("wzgg.cz").as("wzgg.cz"))
                                .add(Projections.groupProperty("wzgg.name").as("wzgg.name"))
                                .add(Projections.groupProperty("wzgg.ggxh").as("wzgg.ggxh"))
                                .add(Projections.groupProperty("wzgg.dw").as("wzgg.dw"))
                                .add(Projections.groupProperty("cd.id").as("cd.id"))
                                .add(Projections.groupProperty("cd.z").as("cd.z"))
                                .add(Projections.groupProperty("xscb").as("xscb"))
                                .add(Projections.groupProperty("sjdj").as("sjdj"))
                                .add(Projections.groupProperty("js").as("js"))
                                .add(Projections.groupProperty("ck_sbsl").as("cksl"))
                                .add(Projections.groupProperty("sbsl").as("sbsl"))
                                .add(Projections.groupProperty("jzfs.id").as("jzfs.id"))
                                .add(Projections.groupProperty("jzfs.z").as("jzfs.z"))
                                .add(Projections.groupProperty("hsj").as("hsj"))
                                .add(Projections.groupProperty("bhsj").as("bhsj"))
                                .add(Projections.groupProperty("jsbhse").as("je"))
                                .add(Projections.groupProperty("jsse").as("se"))
                                .add(Projections.groupProperty("bz").as("bz"))
                        //.add(Projections.sum("jfmxList.dlfje").as("jfmxdlf"))
                ).add(Restrictions.eq("xsd.id", xsdId)).add(Restrictions.eq("yjs", false))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsfyList(Long xsdId) {
        Criteria criteria = getSession().createCriteria(Glfy.class)
                .createAlias("sydw", "sydw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fylb", "fylb", CriteriaSpecification.LEFT_JOIN)
                .createAlias("wljsd", "jsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("sydw.id").as("sydw.id"))
                                .add(Property.forName("sydw.qc").as("sydw.qc"))
                                .add(Property.forName("fylb.id").as("fylb.id"))
                                .add(Property.forName("fylb.fykmjc").as("fylb.fykmjc"))
                                .add(Property.forName("jfsl").as("jfsl"))
                                .add(Property.forName("dj").as("dj"))
                                .add(Property.forName("je").as("je"))
                                .add(Property.forName("bz").as("bz"))
                                .add(Property.forName("jsd.jsdh").as("jsdh"))
                ).add(Restrictions.eq("xsd.id", xsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findXsmxByKh(Kh kh) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("kh.khbm").as("kh.khbm"))
                                .add(Property.forName("kh.kkdsl").as("kkdsl"))
                                .add(Property.forName("xsd.xsdh").as("xsd.xsdh"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Projections.sqlProjection("wzgg3_.cz||' '||wzgg3_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("xscb").as("xscb"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("ck_js").as("ck_js"))
                                .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("kh.id", kh.getId()))
                .add(Restrictions.isNotNull("xsd.id"))
                .add(Restrictions.eq("xsd.deleted", 0))
                .add(Restrictions.eq("xsd.sbgs.id", YgService.getCurrentYgGsId()))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.addOrder(Property.forName("wzgg.code").asc());
        return (List<Map>) criteria.list();
    }

    public void deleteXsmx(Long xsdId) {
        String hql = "update Xsmx set deleted=1 where xsd.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, xsdId);
        query.executeUpdate();
    }

    /**
     * 条件查询销售单
     *
     * @param xsdh
     * @param fhsjStart
     * @param fhsjEnd
     * @param xsjsdId
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Xsd> findXsdSearch(String xsdh, Date fhsjStart, Date fhsjEnd, Long xsjsdId, Long xsgsId, Long sbgsId, Long khId, int pageNo, int pageSize, String erdwMc, String htbm, String xhtbm) {
        Page<Xsd> page = new Page<Xsd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsht", "xsht", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("cksl").as("cksl"))
                                .add(Property.forName("xssl").as("xssl"))
                                .add(Property.forName("xsje").as("xsje"))
                                .add(Property.forName("xsbhsje").as("xsbhsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsht.id").as("xsht_id"))
                                .add(Property.forName("xsht.htbm").as("htbm"))
                                .add(Projections.sqlProjection("xsht11_.xsxt_htbm as xhtbm", new String[]{"xhtbm"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.DATE}))
                                .add(Property.forName("kh.qc").as("kh_qc"))
                                .add(Property.forName("kh.id").as("kh_id"))
                                .add(Property.forName("jhdz").as("jhdz"))
                                .add(Property.forName("gc.id").as("gc_id"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("thck.mc").as("thck_mc"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("dsyfje").as("dsyfje"))
                                .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                                .add(Property.forName("xsgs.jc").as("xsgs_jc"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.jc").as("sbbm_jc"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("dateCreated"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like(xsdh, MatchMode.ANYWHERE));
        }
        if (fhsjStart != null) {
            criteria.add(Restrictions.gt("fhsj", fhsjStart));
        }
        if (fhsjEnd != null) {
            criteria.add(Restrictions.lt("fhsj", fhsjEnd));
        }
        if (htbm != null && StringUtils.isNotEmpty(htbm)) {
            criteria.add(Property.forName("xsht.htbm").like(htbm, MatchMode.START));
        }
        if (xhtbm != null && StringUtils.isNotEmpty(xhtbm)) {
            criteria.add(Property.forName("xsht.xsxt_htbm").like(xhtbm, MatchMode.START));
        }
        if (khId != null) {
            criteria.add(Restrictions.eq("kh.id", khId));
        }
        if (xsgsId != null) {
            criteria.add(Restrictions.eq("xsgs.id", xsgsId));
        }
        if (StringUtils.isNotEmpty(erdwMc)) {
            criteria.add(Property.forName("gc.mc").like(erdwMc, MatchMode.START));
        }
        if (xsjsdId != null) {
            criteria.add(Restrictions.eq("xsjsd.id", xsjsdId));
        } else {
            DetachedCriteria dc = DetachedCriteria.forClass(Xsmx.class);
            dc.setProjection(Projections.property("xsd.id"));
            dc.add(Restrictions.isNull("xsjsd"));
            criteria.add(Subqueries.propertyIn("id", dc));
        }
        criteria.add(Restrictions.eq("yck", true));
        criteria.add(Restrictions.eq("zt", "ypk"));
        criteria.add(Restrictions.eq("sftzd", false));
        criteria.addOrder(Order.asc("xsht.htbm"));
//        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    //字符串转化成日期并且指定是0点0时0分
    private Date praseStrToDate(String str) {
        Date date = null;
        DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
        try {
            date = format1.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
        return date;
    }

    //获取明天的 0点0时0分
    private Date getNextDay(Date time) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(time);
        calendar.add(Calendar.DAY_OF_YEAR, 1);
        Date date = calendar.getTime();
        return date;
    }

    public Page<Map> findXsdDateSearch(String xsdh, Date fhsjStart, Date fhsjEnd, Long xsjsdId, Long xsgsId, Long sbgsId, Long khId, int pageNo, int pageSize, String erdwMc) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String whereSql = "";

        if (xsgsId != null) {
            condition.add(" this_.XSGS_ID = :xsgsId ");
            param.put("xsgsId", xsgsId);
        }
        if (sbgsId != null) {
            condition.add(" this_.SBGS_ID = :sbgsId ");
            param.put("sbgsId", sbgsId);
        }
        if (khId != null) {
            condition.add(" this_.KH_ID = :khId ");
            param.put("khId", khId);
        }

        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        String sqlCountBase = "select count(1) from (select distinct xsht.htbm,xsht.xsxt_htbm as xhtbm\n" +
                " from T_BIZ_XSD this_ left join t_biz_ht xsht on this_.xsht_id = xsht.id  left outer join " +
                " T_SYS_SJZDZ fyzr4_ on this_.FYZR_SJZDZ_ID = fyzr4_.id\n" +
                " left outer join T_SYS_GC gc2_ on this_.GC_ID = gc2_.id\n" +
                " left outer join T_SYS_GSJG kh1_ on this_.KH_ID = kh1_.id\n" +
                " left outer join T_SYS_GSJG sbbm9_ on this_.SBBM_ID = sbbm9_.id\n" +
                " left outer join T_SYS_GSJG sbgs8_ on this_.SBGS_ID = sbgs8_.id\n" +
                " left outer join T_SYS_YG sbr10_ on this_.SBR_ID = sbr10_.id\n" +
                " left outer join T_SYS_CK thck3_ on this_.CK_ID = thck3_.id\n" +
                " left outer join T_SYS_GSJG xsbm6_ on this_.XSBM_ID = xsbm6_.id\n" +
                " left outer join T_SYS_GSJG xsgs5_ on this_.XSGS_ID = xsgs5_.id\n" +
                " left outer join T_SYS_YG xsr7_ on this_.XSR_ID = xsr7_.id\n" +
                " where (this_.deleted != '1') and this_.id in (select this_.XSD_ID as y0_\n" +
                "  from T_BIZ_XSMX this_ where (this_.deleted != '1') and this_.XSJSD_ID is null)\n" +
                " and this_.zt = 'ypk' and this_.sftzd = 0 and (xsht.sfjs = '0' or xsht.sfjs is null)  " + whereSql + ") t ";

        String sqlBase = "select * from " +
                " (select distinct xsht.htbm,xsht.xsxt_htbm as xhtbm,to_char(xsht.qdrq,'yyyy-Mm-dd') as qdrq\n" +
                " from T_BIZ_XSD this_ " +
                " left join t_biz_ht xsht on this_.xsht_id = xsht.id" +
                " left outer join T_SYS_SJZDZ fyzr4_ on this_.FYZR_SJZDZ_ID = fyzr4_.id\n" +
                " left outer join T_SYS_GC gc2_ on this_.GC_ID = gc2_.id\n" +
                " left outer join T_SYS_GSJG kh1_ on this_.KH_ID = kh1_.id\n" +
                " left outer join T_SYS_GSJG sbbm9_ on this_.SBBM_ID = sbbm9_.id\n" +
                " left outer join T_SYS_GSJG sbgs8_ on this_.SBGS_ID = sbgs8_.id\n" +
                " left outer join T_SYS_YG sbr10_ on this_.SBR_ID = sbr10_.id\n" +
                " left outer join T_SYS_CK thck3_ on this_.CK_ID = thck3_.id\n" +
                " left outer join T_SYS_GSJG xsbm6_ on this_.XSBM_ID = xsbm6_.id\n" +
                " left outer join T_SYS_GSJG xsgs5_ on this_.XSGS_ID = xsgs5_.id\n" +
                " left outer join T_SYS_YG xsr7_ on this_.XSR_ID = xsr7_.id\n" +
                " where (this_.deleted != '1') and this_.id in (select this_.XSD_ID as y0_\n" +
                "  from T_BIZ_XSMX this_ where (this_.deleted != '1') and this_.XSJSD_ID is null)\n" +
                " and this_.zt = 'ypk' and this_.sftzd = 0 and (xsht.sfjs = '0' or xsht.sfjs is null) " + whereSql + " ) t ";

        String countSql = sqlCountBase;
        String resultSql = sqlBase;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;

    }

    //    销售单合计
    public List<Map> findSumXsd(String xsdh, Date fhsjStart, Date fhsjEnd, Long xshtId, Long khId, Long gcId, Long sbgsId, Long sbbmId, Long sbrId, String zt, String cxzt, String sftzd, String xslx, Boolean dlkhxsd, int type) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlbase = "select sum(xsjs) as \"xsjs\",sum(xssl) as \"xssl\",sum(xsje) as \"xsje\",sum(gylfyje) as \"gylfyje\" from (";

        String sql1 = "select sum(xsmx.js) as xsjs,sum(xsmx.sbsl) as xssl, sum(decode(nvl(xsmx.sjdj,0),0,xsmx.hsj,xsmx.sjdj)*xsmx.sbsl) as xsje," +
                "0 as gylfyje from t_biz_xsd t " +
                " left outer join t_biz_xsmx xsmx on t.id = xsmx.xsd_id and xsmx.deleted!=1";

        String sql2 = "select 0 as xsjs,0 as xssl, 0 as xsje,sum(fy.je) as gylfyje from t_biz_xsd t " +
                " left outer join t_biz_glfy fy on t.ID = fy.xsd_id and fy.deleted!=1";


        if (StringUtils.isNotEmpty(xsdh)) {
            condition.add(" t.xsdh like :xsdh ");
            param.put("xsdh", "%" + xsdh + "%");
        }

        if (fhsjStart != null) {
            condition.add(" t.date_created>= :fhsjStart ");
            param.put("fhsjStart", sDaysAfterNowDate(fhsjStart));
        } /*else {
            Date start = sDaysAfterNowDate(new Date());
            condition.add(" t.date_created>= :fhsjStart ");
            param.put("fhsjStart", start);
        }*/
        if (fhsjEnd != null) {
            condition.add(" t.date_created<= :fhsjEnd ");
            param.put("fhsjEnd", jDaysAfterNowDate(fhsjEnd));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            condition.add(" t.date_created<= :fhsjEnd ");
            param.put("fhsjEnd", end);
        }
        if (cxzt != null && !cxzt.isEmpty() && "wcx".equals(cxzt)) {
            condition.add(" (t.sftzd is null or t.sftzd=0) ");
        } else if (cxzt != null && !cxzt.isEmpty() && "ycx".equals(cxzt)) {
            condition.add(" t.sftzd=1 ");
        }

        if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }
        if (xshtId != null) {
            condition.add(" t.xsht_id=:xsht_id");
            param.put("xsht_id", xshtId);
        }

        if (sbgsId != null) {
            condition.add(" t.sbgs_id=:sbgs_id ");
            param.put("sbgs_id", sbgsId);
        }
        if (sbbmId != null) {
            condition.add(" t.sbbm_id=:sbbm_id ");
            param.put("sbbm_id", sbbmId);
        }
        if (sbrId != null) {
            condition.add(" t.sbr_id=:sbr_id ");
            param.put("sbr_id", sbrId);
        }

        if (khId != null) {
            condition.add(" t.kh_id=:kh_id ");
            param.put("kh_id", khId);
        }

        if (gcId != null) {
            condition.add(" t.gc_id=:gc_id ");
            param.put("gc_id", gcId);
        }

        if (StringUtils.isNotEmpty(xslx)) {
            condition.add(" t.xslx=:xslx");
            param.put("xslx", xslx);
        } else {
            condition.add(" t.xslx!='nbxs'");
        }

        if (sftzd != null) {
            condition.add(" t.sftzd=0");
        } else {
            condition.add(" t.zt='ysp' and (t.yjs is null or t.yjs=0)");
        }

        // 出库单管理
        if (type == 1) {
            condition.add(" t.zt='ybc' or t.zt='ysh'");

        } else if (type == 2) {
            condition.add(" t.zt='ygk' or t.zt='ytj'");

        } else {
            condition.add(" t.zt='ysp' or t.zt='ypk'");

        }

        //condition.add(" t.zt='ysp' or t.zt='ypk'");

       /* if (dlkhxsd != null && dlkhxsd) {
            condition.add(" t.sbgs_id in (select id from T_SYS_GSJG where type='nbgs' and deleted!=1) ");
        }*/

        String dataRegion = createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql1 += " where t.deleted!=1 and " + whereSql + dataRegion;
        sql2 += " where t.deleted!=1 and " + whereSql + dataRegion;

        String sql = sqlbase + sql1 + " union all " + sql2 + ")";

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    private String createSQLDataRegion(String joinStr, String alias) {
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);


            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        } else {
            alias = StringUtils.isEmpty(alias) ? "" : alias + ".";
            joinStr = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ( {1}CREATEORGAN_ID =" + YgService.getCurrentYgZgGsId();
                    break;
                case 2:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    break;
                case 3:
                    regionSql += "  {0} ( {1}CREATEDEPARTMENT_ID =" + YgService.getCurrentYgGsId();
                    regionSql += "  and   {1}createuser_id =" + YgService.getCurrentYg().getId();
                    break;
            }

            if (YgService.getCurrentYgCxfw() != 0) {
                regionSql += " or {1}sbgs_id =" + YgService.getCurrentYgZgGsId() + ")";
            }

            return regionSql.replace("{0}", joinStr).replace("{1}", alias);
        }
    }

    public Page<Xsd> findXsdXspsList(Long thckId, String xspsKh, Long sbgsId, String pszt, String xsdh, String psdh, Sjzdz fyzr, int pageNo, int pageSize) {
        Page<Xsd> page = new Page<Xsd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("lastUpdated");

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("thck", "thck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fyzr", "fyzr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsgs", "xsgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsbm", "xsbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsr", "xsr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("psd", "psd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("psd.wls", "wls", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("xssl").as("xssl"))
                                .add(Property.forName("cksl").as("cksl"))
                                .add(Property.forName("xslx").as("xslx"))
                                .add(Property.forName("xsje").as("xsje"))
                                .add(Property.forName("xsbhsje").as("xsbhsje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsht.id").as("xsht_id"))
                                .add(Projections.sqlProjection("jhdz ||'/'|| shry ||'/'|| shrdh as jss", new String[]{"jss"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("xsdh as xsdh", new String[]{"xsdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("fhsj as fhsj", new String[]{"fhsj"}, new Type[]{Hibernate.DATE}))
                                .add(Property.forName("psd.psdh").as("psdh"))
                                .add(Property.forName("psd.id").as("psd_id"))
                                .add(Property.forName("psd.psfy").as("psfy"))
                                .add(Property.forName("wls.qc").as("wls_qc"))
                                .add(Property.forName("psd.shch").as("shch"))
                                .add(Property.forName("psd.jsy").as("jsy"))
                                .add(Property.forName("psd.lxdh").as("lxdh"))
                                .add(Property.forName("jhsj").as("jhsj"))
                                .add(Property.forName("kh.qc").as("kh_qc"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("kh.id").as("kh_id"))
                                .add(Property.forName("jhdz").as("jhdz"))
                                .add(Property.forName("gc.mc").as("gc_mc"))
                                .add(Property.forName("thck.mc").as("thck_mc"))
                                .add(Property.forName("fyzr.z").as("fyzr_z"))
                                .add(Property.forName("dsyfje").as("dsyfje"))
                                .add(Projections.sqlProjection("this_.yps as yps", new String[]{"yps"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("xsgs.qc").as("xsgs_qc"))
                                .add(Property.forName("xsbm.qc").as("xsbm_qc"))
                                .add(Property.forName("xsr.xm").as("xsr_xm"))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("dateCreated").as("dateCreated"))
                                .add(Property.forName("createUser.xm").as("createUser_xm"))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xsdh)) {
            criteria.add(Property.forName("xsdh").like("%" + xsdh + "%", MatchMode.START));
        }
        if (StringUtils.isNotEmpty(psdh)) {
            criteria.add(Property.forName("psd.psdh").like("%" + psdh + "%", MatchMode.START));
        }
        if (StringUtils.isNotEmpty(pszt)) {
            if (pszt != null && !pszt.isEmpty() && "yps".equals(pszt)) {
                criteria.add(Restrictions.eq("yps", true));
            } else if (pszt != null && !pszt.isEmpty() && "wps".equals(pszt)) {
                criteria.add(Restrictions.eq("yps", false));
            }
        }
        if (sbgsId != null) {
            criteria.add(Restrictions.eq("xsgs.id", sbgsId));
        }
        if (thckId != null) {
            criteria.add(Restrictions.eq("thck.id", thckId));
        }
        if (StringUtils.isNotEmpty(xspsKh)) {
            criteria.add(Property.forName("kh.qc").like(xspsKh, MatchMode.START));
        }
        criteria.add(Restrictions.eq("fyzr", fyzr));
        //   criteria.add(Restrictions.or(Restrictions.eq("zt", "ysp"), Restrictions.eq("zt", "yjs")));
        criteria.add(Restrictions.eq("sftzd", false));
        RegionDao.createDataRegion(criteria);
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    /**
     * 查询业务收入
     *
     * @param sdate
     * @param htType
     * @return
     */
    public List findYwsr(String sdate, String htType) {
        String ywsrSql = "";
        if (null != htType && htType.equals("bgxs")) {
            ywsrSql = " select nvl(sum(tm.sbsl*tm.bhsj),0) as sumje  from t_biz_jsd tj,t_biz_xsmx tm,t_biz_xsd ts " +
                    " where tj.id =tm.xsjsd_id and tm.xsd_id = ts.id and ts.deleted=0 and ts.yjs= 1 and  to_char(ts.date_created, 'yyyy-mm') = ? and ts.xslx = ? ";
        } else {
            ywsrSql = " select nvl(sum(tm.sbsl*tm.bhsj),0) as sumje  from t_biz_jsd tj,t_biz_xsmx tm,t_biz_xsd ts " +
                    " where tj.id =tm.xsjsd_id and tm.xsd_id = ts.id and ts.deleted=0 and ts.yjs= 1 and to_char(ts.date_created, 'yyyy-mm') = ? and ts.xslx != ? ";
        }
        return this.findBySQL(ywsrSql, sdate, "bgxs");
    }

    public List<Map> findKhByXsd(Long gsjgId, Long sbgsId) {
        String hql = "select distinct kh.id as id,kh.jc as jc, qyfr.nsh as qyfr_nsh from Xsd xsd left join xsd.kh kh left join kh.qyfr qyfr where xsd.yjs=false and xsd.xsgs.id=? ";
        Query query = this.getSession().createQuery(hql);
        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        query.setLong(0, gsjgId);
        return query.list();
    }

    /**
     * 根据客户的ID查询客户纳税号
     *
     * @param gsjgId
     * @return
     */
    public List<Map> findKhById(Long gsjgId) {
        String hql = "select fr.nsh as nsh from Gsjg g left join g.qyfr fr where g.id = ?";
        Query query = this.getSession().createQuery(hql);
        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        query.setLong(0, gsjgId);
        return query.list();
    }


    public void calcKhSL(Xsd xsd, String type) throws RuntimeException {
        List<Xsmx> xsmxList = xsd.getXsmxList();
        Map<Long, Double> khSl = new HashMap<Long, Double>();
        if (xsmxList != null) {
            for (Xsmx xsmx : xsmxList) {
                if (xsmx.getKh() != null) {
                    java.text.DecimalFormat df = new java.text.DecimalFormat("#0.0000");
                    Double value = khSl.get(xsmx.getKh().getId());
                    String sbslstr = df.format(xsmx.getSbsl());
                    if (value != null) {
                        String valuestr = df.format(value);
                        value = Double.parseDouble(valuestr);
                        khSl.put(xsmx.getKh().getId(), value + Double.parseDouble(sbslstr));
                    } else {
                        khSl.put(xsmx.getKh().getId(), Double.parseDouble(sbslstr));
                    }
                }
            }
        }

        if (type.equals("add")) {
            for (Long id : khSl.keySet()) {
                this.khDao.updateKhKdslAdd(id, khSl.get(id));
            }
        } else {
            for (Long id : khSl.keySet()) {
                Kh kh = this.khDao.get(id);
                if (kh.getKkdsl() - khSl.get(id) < 0)
                    throw new RuntimeException("提交数量大于可开单数量，无法提交！");
            }
            for (Long id : khSl.keySet()) {
                this.khDao.updateKhKdsl(id, khSl.get(id));
            }
        }
    }


    /**
     * 配款
     */
    public void executeSfkpk(Long xsdId) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call R_BIZ_SFK_PK(?)}");
            call.setLong(1, xsdId);
            call.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource(null, call, con);
        }
    }

    public void executeEjzhZk(Long xsdId) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call R_BIZ_EJZH_ZK(?)}");
            call.setLong(1, xsdId);
            call.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource(null, call, con);
        }
    }

    /**
     * 挂款明细
     *
     * @param xsd
     * @return
     */
    public List<Map> findXsdgkmx(Xsd xsd) {
        String sql;
        if (xsd.getXsht().getZddhcght().getBzjfs() == 0) {
            sql = "select * from (select sf.bm as bm,sf.sfje ,sf.ye, jf.ghje as sjje,jf.sfk_id as sfkid,to_char(sf.khfksj , 'yyyy-MM-dd') as fksj ," +
                    " (select s.z from t_sys_sjzdz s where sf.kxsy_sjzdz_id = s.id) as kxsy," +
                    " (select sj.z from t_sys_sjzdz sj where sf.fkfs_sjzdz_id = sj.id) as fkfs,'货款' as syfs " +
                    " from t_biz_sfk sf ,t_biz_jfmx jf where sf.id = jf.sfk_id and jf.xsd_id = ? " +
                    " union all " +
                    " select sf.bm as bm,sf.sfje ,sf.ye, dlf.dlf as sjje,dlf.sfkid as sfkid,to_char(sf.khfksj , 'yyyy-MM-dd') as fksj ," +
                    " (select s.z from t_sys_sjzdz s where sf.kxsy_sjzdz_id = s.id) as kxsy, " +
                    " (select sj.z from t_sys_sjzdz sj where sf.fkfs_sjzdz_id = sj.id) as fkfs,'代理费用' as syfs " +
                    " from t_biz_sfk sf ,t_biz_dlf dlf where sf.id = dlf.sfkid and dlf.deleted =0 and dlf.xsd_id = ?) " +
                    " order by sfkid desc ";
            return (List<Map>) this.createSQLQuery(sql, xsd.getId(), xsd.getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        } else if (xsd.getXsht().getZddhcght().getBzjfs() == 1) {
            sql = "select sf.bm as bm,sf.sfje ,sf.ye, ab.je as sjje,to_char(sf.khfksj , 'yyyy-MM-dd') as fksj, " +
                    " (select s.z from t_sys_sjzdz s where sf.kxsy_sjzdz_id = s.id) as kxsy," +
                    " (select sj.z from t_sys_sjzdz sj where sf.fkfs_sjzdz_id = sj.id) as fkfs,'货款' as syfs " +
                    " from t_biz_sfk sf ,t_biz_ablbzjgk ab where sf.id = ab.sfk_id and ab.deleted = 0 and ab.xsd_id = ? " +
                    " order by ab.sfk_id desc ";
        } else {
            sql = "select sf.bm as bm,sf.sfje ,sf.ye, wb.je as sjje,to_char(sf.khfksj , 'yyyy-MM-dd') as fksj," +
                    " (select s.z from t_sys_sjzdz s where sf.kxsy_sjzdz_id = s.id) as kxsy," +
                    " (select sj.z from t_sys_sjzdz sj where sf.fkfs_sjzdz_id = sj.id) as fkfs,'货款' as syfs " +
                    " from t_biz_sfk sf ,t_biz_wbzjgk wb where sf.id = wb.sfk_id and wb.deleted = 0 and wb.xsd_id = ? " +
                    " order by wb.sfk_id desc ";
        }
        return (List<Map>) this.createSQLQuery(sql, xsd.getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


    public List<Map> findJfmxList(Long xsdId) {
        String sql = "select gg.name, gg.cz,gg.ggxh, jfmx.zl,jfmx.hsj,xsmx.sjdj,jfmx.khfksj,jfmx.tzfksj,jfmx.txts,jfmx.txje, " +
                " jfmx.sjzyts , jfmx.jszyts, jfmx.dlfje, jfmx.ghje , kh.carno,kh.bindno,kh.xsxt_xsdh,sfk.sfje, jfmx.yhje," +
                " (select nvl(jf.dlfje,0)+nvl(jf.txje,0)+nvl(jf.ghje,0)  from t_biz_jfmx jf where jf.id = jfmx.id)  as ghzje," +
                "  sz.z || '(' || z.z || ')'  as bz " +
                "  from t_biz_jfmx jfmx " +
                "  left join t_biz_xsmx xsmx on xsmx.id=jfmx.xsdmx_id " +
                "  left join t_biz_kh kh on kh.id = xsmx.kh_id " +
                "  left join t_sys_gg  gg on gg.id= kh.jtwz_id " +
                "  left join t_biz_sfk sfk on sfk.id=jfmx.sfk_id " +
                " left join t_sys_sjzdz z  on sfk.fkfs_sjzdz_id = z.id  " +
                " left join t_sys_sjzdz sz  on sfk.kxsy_sjzdz_id = sz.id " +
                "  where jfmx.xsd_id = ?";
        return (List<Map>) this.createSQLQuery(sql, xsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


    /**
     * 计算代理费
     *
     * @param xshtId
     * @param xsmxId
     * @param jfmxId
     */
    public String executeDLF(Long xshtId, Long xsmxId, Long jfmxId, Xsd xsd) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            System.out.print("R_BIZ_DLF开始了...");
            // 判断是否线上还是线上的赎货
            if (StringUtils.equals("xsms", xsd.getXsht().getHtms())) {
                call = con.prepareCall("{Call R_BIZ_DLF_XS(?,?,?,?)}");
            } else {
                call = con.prepareCall("{Call R_BIZ_DLF_XX(?,?,?,?)}");
            }
            call.setLong(1, xshtId);
            call.setLong(2, xsmxId);
            call.setLong(3, jfmxId);
            call.registerOutParameter(4, Types.VARCHAR);
            call.execute();
            String testPrint = call.getString(4);
            if (testPrint.equals("-1")) {
                throw new RuntimeException("代理费计算失败，可能没有配置公式：" + testPrint);

            } else {
                return testPrint;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(null, call, con);
        }
    }

    /**
     * 计算贴息
     *
     * @param jfmxId
     * @throws Exception
     */
    public void executeTx(Long jfmxId) {
        Connection con = null;
        CallableStatement call = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call R_BIZ_TX(?)}");
            call.setLong(1, jfmxId);
            call.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(null, call, con);
        }
    }


    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }


    public List<Map> findSumZe(Long xsdId) {
        String sql = "select sum(x.sjdj * x.sbsl) ze from t_biz_xsmx x where x.xsd_id = ? and x.deleted = 0";
        return (List<Map>) this.createSQLQuery(sql, xsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public Double findZjzyje(Xsd xsd) {
        String sql = "select nvl(round(sum(htmx.hsj*htmx.sbsl),4),0) as htje from t_biz_htmx htmx ,t_biz_ht h " +
                "where htmx.ht_id = h.id and h.id = ?";
        Map htjeMap = (Map) this.createSQLQuery(sql, xsd.getXsht().getZddhcght().getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list().get(0);

        String sql1 = "select nvl(round(sum(t.zjfpje),4),0) as xsje  from t_biz_jfmx t where t.xsht_id =? ";
        Map xsjeMap = (Map) this.createSQLQuery(sql1, xsd.getXsht().getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list().get(0);
        Double ce = Double.valueOf(htjeMap.get("HTJE").toString()) - Double.valueOf(xsjeMap.get("XSJE").toString());
        return ce;
    }

    /**
     * 客户id
     *
     * @param khId
     * @return
     */
    public Map findXsdTzdInfo(Long khId) {
        String sql = "select x.zh,x.lsh,x.thrs,x.jbr,x.sfzhm,x.cars from t_biz_xsd x  where x.id = " +
                " (select max(id) from t_biz_xsd xs where xs.lsh is not null and xs.sfzhm is not null and xs.kh_id = ?) ";
        List<Map> tzsList = (List<Map>) this.createSQLQuery(sql, khId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
        if (null != tzsList && tzsList.size() > 0) {
            Map map = tzsList.get(0);
            return map;
        }
        return null;
    }

    public List findSurplusKh(Long cghtId, List<Xsmx> xsmxList) {
        String khIds = "";
        if (null != xsmxList && xsmxList.size() > 0) {
            for (Xsmx xsmx : xsmxList) {
                khIds += xsmx.getKh().getId() + ",";
            }
        }
        khIds += "0";
        String sql = "select id from t_biz_kh k where k.ybc = 0 and k.cght_id = ? and k.id not in (" + khIds + ")";
        return this.createSQLQuery(sql, cghtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    /**
     * 交易系统销售单查询接口：
     * 根据交易订单号查询销售单信息
     *
     * @param orderId
     * @return
     */
    public List<Xsd> findXsdListByOrderId(String orderId) {
        String hql = " from Xsd where orderId = ? and zt = 'ypk' ";
        return this.find(hql, orderId);
    }

    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }

    @Autowired
    public void setKhDao(KhDao khDao) {
        this.khDao = khDao;
    }


}


